package com.yd.app.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * Excel相关的方法
 * @author huy
 * @date 2016年8月17日
 */
public class ExcelUtil {
	/**
	 * 对外提供读取excel 的方法
	 * */
	public static List<List<Object>> readExcel(File file) throws IOException {
		String fileName = file.getName();
		String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
				.substring(fileName.lastIndexOf(".") + 1);
		if ("xls".equals(extension)) {
			return read2003Excel(file);
		} else if ("xlsx".equals(extension)) {
			return read2007Excel(file);
		} else {
			throw new IOException("不支持的文件类型");
		}
	}

	/**
	 * 读取 office 2003 excel
	 * 
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	private static List<List<Object>> read2003Excel(File file)
			throws IOException {
		List<List<Object>> list = new LinkedList<List<Object>>();
		HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
		HSSFSheet sheet = hwb.getSheetAt(0);
		Object value = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		for (int i = sheet.getFirstRowNum()+1; i <= sheet.getPhysicalNumberOfRows()+1; i++) {
			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			List<Object> linked = new LinkedList<Object>();
			boolean flag=false;
			for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
				cell = row.getCell(j);
				if (cell == null) {
					flag=false;
					continue;
				}
				flag=true;
				DecimalFormat df = new DecimalFormat("0");// 格式化 number String
															// 字符
				SimpleDateFormat sdf = new SimpleDateFormat(
						"yyyy-MM-dd");// 格式化日期字符串
				DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
				switch (cell.getCellType()) {
				case XSSFCell.CELL_TYPE_STRING:
					//System.out.println(i + "行" + j + " 列 is String type");
					value = cell.getStringCellValue();
					break;
				case XSSFCell.CELL_TYPE_NUMERIC:
					/*System.out.println(i + "行" + j
							+ " 列 is Number type ; DateFormt:"
							+ cell.getCellStyle().getDataFormatString());*/
					if ("@".equals(cell.getCellStyle().getDataFormatString())) {
						value = df.format(cell.getNumericCellValue());
					} else if ("General".equals(cell.getCellStyle()
							.getDataFormatString())) {
						value = nf.format(cell.getNumericCellValue());
					} else {
						value = sdf.format(HSSFDateUtil.getJavaDate(cell
								.getNumericCellValue()));
					}
					break;
				case XSSFCell.CELL_TYPE_BOOLEAN:
					//System.out.println(i + "行" + j + " 列 is Boolean type");
					value = cell.getBooleanCellValue();
					break;
				case XSSFCell.CELL_TYPE_BLANK:
					//System.out.println(i + "行" + j + " 列 is Blank type");
					value = "";
					break;
				default:
					//System.out.println(i + "行" + j + " 列 is default type");
					value = cell.toString();
				}
				if (value == null || "".equals(value)) {
					flag=false;
					continue;
				}
				flag=true;
				linked.add(value);
			}
			if(flag){
				linked.add(i);
			}
			list.add(linked);
		}
		return list;
	}

	/**
	 * 读取Office 2007 excel
	 * */
	private static List<List<Object>> read2007Excel(File file)
			throws IOException {
		List<List<Object>> list = new LinkedList<List<Object>>();
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
		// 读取第一章表格内容
		XSSFSheet sheet = xwb.getSheetAt(0);
		Object value = null;
		XSSFRow row = null;
		XSSFCell cell = null;
		//跳过表头
		for (int i = sheet.getFirstRowNum()+1; i <= sheet.getPhysicalNumberOfRows()+1; i++) {
			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			List<Object> linked = new LinkedList<Object>();
			boolean flag=false;
			for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
				cell = row.getCell(j);
				if (cell == null) {
					continue;
				}
				DecimalFormat df = new DecimalFormat("0");// 格式化 number String
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
				DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
				switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						//System.out.println(i + "行" + j + " 列 is String type");
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						//System.out.println(i + "行" + j + " 列 is Number type ; DateFormt:" + cell.getCellStyle().getDataFormatString());
						value = df.format(cell.getNumericCellValue());
//						if ("@".equals(cell.getCellStyle().getDataFormatString())) {
//							
//						} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
//							value = nf.format(cell.getNumericCellValue());
//						} else {
//							value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
//						}
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						//System.out.println(i + "行" + j + " 列 is Boolean type");
						value = cell.getBooleanCellValue();
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						//System.out.println(i + "行" + j + " 列 is Blank type");
						value = "";
						break;
					default:
						//System.out.println(i + "行" + j + " 列 is default type");
						value = cell.toString();
				}
				if (value == null || "".equals(value)) {
					flag=false;
					continue;
				}
				flag=true;
				linked.add(value);
			}
			if(flag){
				linked.add(i);
			}
			list.add(linked);
		}
		return list;
	}
	
	/**
     * 生成excel并下载
     */
    public static void export2007Excel(List<List<Object>> list,String path_temp,String file_path,String fileName){
		File newFile = createNewFile(path_temp,file_path,fileName);
        //新文件写入数据
        InputStream is = null;
        XSSFWorkbook workbook = null;
        XSSFSheet sheet = null;
        try {
            is = new FileInputStream(newFile);
            workbook = new XSSFWorkbook(is);
            //获取第一个sheet
            sheet = workbook.getSheetAt(0);
        } catch (Exception e1) {
            e1.printStackTrace();
        }
        if(sheet != null){
            try {
                //写数据
                FileOutputStream fos = new FileOutputStream(newFile);
                int rowId=1;
                for(int i=0;i<list.size();i++){
                	List<Object> rowList=list.get(i);
                	if(rowList!=null){
						XSSFRow row = sheet.createRow(rowId);
						//sheet.setColumnWidth(2, 4000);//设置列宽
						row.setHeight((short) 1500);
						for(int j=0;j<rowList.size();j++){
							XSSFCell name = row.createCell(j,XSSFCell.CELL_TYPE_STRING);
							if(i>2&&j==3){//图片  临时测试使用  第二列为图片
								ImageExcelUtil.writeImage(3,i+1,(String)rowList.get(j),workbook,sheet);
							}else{
								name.setCellValue((String)rowList.get(j));
							}
						}
                        rowId=rowId+1;
                	}
                }
                workbook.write(fos);
                fos.flush();
                fos.close();
            }
            catch(Exception e) {
                e.printStackTrace();
            }finally {
                try {
                    if (null != is) {
                        is.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        //删除创建的新文件
        //deleteFile(newFile); 
    }
    /**
    * 复制文件
    * 
    * @param s
    * 源文件
    * @param t
    * 复制到的新文件
    */

    public static void fileChannelCopy(File s, File t) {
        try {
            InputStream in = null;
            OutputStream out = null;
            try {
                in = new BufferedInputStream(new FileInputStream(s),1024);
                out = new BufferedOutputStream(new FileOutputStream(t),1024);
                byte[] buffer = new byte[1024];
                int len; 
                while ((len=in.read(buffer))!=-1) {
                    out.write(buffer,0,len);
                }
            } finally {
                if (null != in) {
                    in.close();
                }
                if (null != out) {
                    out.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 读取excel模板，并复制到新文件中供写入和下载
     * @return
     */
    public static File createNewFile(String path_temp,String new_file_path,String fileName){
        //读取模板，并赋值到新文件************************************************************
        //文件模板路径


		/*String path = "C:\\Users\\Administrator\\Desktop\\test";
        String fileName="template.xlsx";*/
        File file=new File(path_temp);

        //保存文件的路径
      //  String realPath ="C:\\Users\\Administrator\\Desktop\\test";
		String realPath =new_file_path;
        //新的文件名
        String newFileName = fileName;
        //判断路径是否存在
        File dir = new File(realPath);
        if(!dir.exists()){
            dir.mkdirs();
        }
        //写入到新的excel 
        File newFile = new File(realPath, newFileName);
        try {
            newFile.createNewFile();
            //复制模板到新文件
            fileChannelCopy(file, newFile);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return newFile;
    }

    /**
     * 下载成功后删除
     * 
     * @param files
     */
    public static void deleteFile(File... files) {
        for (File file : files) {
            if (file.exists()) {
                file.delete();
            }
        }
    }
}
